﻿using System;
using System.Collections.Generic;
using System.Text;

namespace AdoHelper
{
    [Serializable]
    public class SelectBuilder
    {
        private bool isDistinct = false;
        public bool IsDistinct
        {
            get { return isDistinct; }
            set { isDistinct = value; }
        }

        private int? top = null;
        public int? Top
        {
            get { return top; }
            set { top = value; }
        }

        private string intoTableName = "";
        public string IntoTableName
        {
            get { return intoTableName; }
            set { intoTableName = value; }
        }

        private SelectClause select = new SelectClause();
        public SelectClause Select
        {
            get { return select; }
        }

        private FromClause from = new FromClause();
        public FromClause From
        {
            get { return from; }
        }
        
        private WhereClause where = new WhereClause();
        public WhereClause Where
        {
            get { return where; }
            set { where = value; }
        }
        
        private GroupByClause groupBy = new GroupByClause();
        public GroupByClause GroupBy
        {
            get { return groupBy; }
        }

        private HavingClause having = new HavingClause();
        public HavingClause Having
        {
            get { return having; }
        }

        private OrderByClause orderBy = new OrderByClause();
        public OrderByClause OrderBy
        {
            get { return orderBy; }
        }

        public string Statement
        {
            get
            {
                string s = "";
                if (this.select.ToString() != "")
                {
                    s += "SELECT ";
                    if (this.isDistinct)
                    {
                        s += "DISTINCT ";
                    }
                    if (this.top.HasValue)
                    {
                        s += "TOP (" + this.top.Value + ") ";
                    }
                    s += this.select.ToString() + "\r\n";
                }
                if (this.intoTableName != "")
                {
                    s += "INTO " + this.intoTableName + "\r\n";
                }
                if (this.from.ToString() != "")
                {
                    s += "FROM "+this.from.ToString()+ "\r\n";
                }
                if (this.where.ToString() != "")
                {
                    s += "WHERE " + this.where.ToString() + "\r\n";
                }
                if (this.groupBy.ToString() != "")
                {
                    s += "GROUP BY " + this.groupBy.ToString() + "\r\n";
                }
                if (this.having.ToString() != "")
                {
                    s += "HAVING " + this.having.ToString() + "\r\n";
                }
                if (this.orderBy.ToString() != "")
                {
                    s += "ORDER BY " + this.orderBy.ToString() + "\r\n";
                }
                return s;
            }
        }

        public void Reset()
        {
            this.isDistinct = false;
            this.top = null;
            this.select.Clear();
            this.from.Clear();
            this.where.Clear();
            this.groupBy.Clear();
            this.orderBy.Clear();
            this.having.Clear();
        }


        public override string ToString()
        {
            return this.Statement;
        }
    }
    [Serializable]
    public class SelectClause
    {
        private string totalExpression="";
        private int count = 0;
        private int newlineLimit = 5;
        public int NewlineLimit
        {
            get { return newlineLimit; }
            set { newlineLimit = value; }
        }
        
        public void Add(params string[] expressions)
        {
            for (int i = 0; i < expressions.Length; i++)
            {
                if(expressions[i].Trim()!="")
                {
                    this.count++;
                    if (totalExpression != "")
                    {
                        totalExpression += ", ";
                    }
                    if (this.count== this.newlineLimit)
                    {
                        this.count = 0;
                        totalExpression += "\r\n";
                    }
                    totalExpression += expressions[i];
                }
            }
        }
        public void Clear()
        {
            this.count = 0;
            this.totalExpression = "";
        }
        public override string ToString()
        {
            return totalExpression;
        }
    }
    [Serializable]
    public class FromClause
    {
        private string totalExpression = "";
        public void InnerJoin(params string[] expressions)
        {
            for (int i = 0; i < expressions.Length; i++)
            {
                if (expressions[i].Trim() != "")
                {
                    if (totalExpression != "")
                    {
                        totalExpression += "\r\nINNER JOIN ";
                    }
                    totalExpression += expressions[i];
                }
            }
        }
        public void LeftOuterJoin(params string[] expressions)
        {
            for (int i = 0; i < expressions.Length; i++)
            {
                if (expressions[i].Trim() != "")
                {
                    if (totalExpression != "")
                    {
                        totalExpression += "\r\nLEFT OUTER JOIN ";
                    }
                    totalExpression += expressions[i];
                }
            }
        }
        public void RightOuterJoin(params string[] expressions)
        {
            for (int i = 0; i < expressions.Length; i++)
            {
                if (expressions[i].Trim() != "")
                {
                    if (totalExpression != "")
                    {
                        totalExpression += "\r\nRIGHT OUTER JOIN ";
                    }
                    totalExpression += expressions[i];
                }
            }
        }
        public void CrossJoin(params string[] expressions)
        {
            for (int i = 0; i < expressions.Length; i++)
            {
                if (expressions[i].Trim() != "")
                {
                    if (totalExpression != "")
                    {
                        totalExpression += "\r\nCROSS JOIN ";
                    }
                    totalExpression += expressions[i];
                }
            }
        }

        public void Clear()
        {
            this.totalExpression = "";
        }
        public override string ToString()
        {
            return totalExpression;
        }
    }
    [Serializable]
    public class WhereClause
    {
        private string totalExpression = "";
        public void AND(params string[] expressions)
        {
            for (int i = 0; i < expressions.Length; i++)
            {
                if (expressions[i].Trim() != "")
                {
                    if (totalExpression != "")
                    {
                        totalExpression += "\r\nAND ";
                    }
                    totalExpression +="("+ expressions[i]+")";
                }
            }
        }
        public void OR(params string[] expressions)
        {
            for (int i = 0; i < expressions.Length; i++)
            {
                if (expressions[i].Trim() != "")
                {
                    if (totalExpression != "")
                    {
                        totalExpression += "\r\nOR ";
                    }
                    totalExpression += "(" + expressions[i] + ")";
                }
            }
        }
        public void NOT(params string[] expressions)
        {
            for (int i = 0; i < expressions.Length; i++)
            {
                if (expressions[i].Trim() != "")
                {
                    if (totalExpression != "")
                    {
                        totalExpression += "\r\nAND NOT ";
                    }
                    else
                    {
                        totalExpression += "\r\nNOT ";
                    }
                    totalExpression += "(" + expressions[i] + ")";
                }
            }
        }
        public void ANDNot(params string[] expressions)
        {
            for (int i = 0; i < expressions.Length; i++)
            {
                if (expressions[i].Trim() != "")
                {
                    if (totalExpression != "")
                    {
                        totalExpression += "\r\nAND NOT ";
                    }
                    totalExpression += "(" + expressions[i] + ")";
                }
            }
        }
        public void ORNot(params string[] expressions)
        {
            for (int i = 0; i < expressions.Length; i++)
            {
                if (expressions[i].Trim() != "")
                {
                    if (totalExpression != "")
                    {
                        totalExpression += "\r\nOR NOT ";
                    }
                    totalExpression += "(" + expressions[i] + ")";
                }
            }
        }
        public void Clear()
        {
            this.totalExpression = "";
        }
        public override string ToString()
        {
            return totalExpression;
        }
    }
    [Serializable]
    public class GroupByClause
    {
        private string totalExpression = "";
        private int count = 0;
        private int newlineLimit = 5;
        public int NewlineLimit
        {
            get { return newlineLimit; }
            set { newlineLimit = value; }
        }
        public void Add(params string[] expressions)
        {
            for (int i = 0; i < expressions.Length; i++)
            {
                if (expressions[i].Trim() != "")
                {
                    this.count++;
                    if (totalExpression != "")
                    {
                        totalExpression += ", ";
                    }
                    if (this.count == this.newlineLimit)
                    {
                        this.count = 0;
                        totalExpression += "\r\n";
                    }
                    totalExpression += expressions[i];
                }
            }
        }
        public void Clear()
        {
            this.count = 0;
            this.totalExpression = "";
        }
        public override string ToString()
        {
            return totalExpression;
        }
    }
    [Serializable]
    public class HavingClause
    {
        private string totalExpression = "";
        public void AND(params string[] expressions)
        {
            for (int i = 0; i < expressions.Length; i++)
            {
                if (expressions[i].Trim() != "")
                {
                    if (totalExpression != "")
                    {
                        totalExpression += "\r\nAND ";
                    }
                    totalExpression += "(" + expressions[i] + ")";
                }
            }
        }
        public void OR(params string[] expressions)
        {
            for (int i = 0; i < expressions.Length; i++)
            {
                if (expressions[i].Trim() != "")
                {
                    if (totalExpression != "")
                    {
                        totalExpression += "\r\nOR ";
                    }
                    totalExpression += "(" + expressions[i] + ")";
                }
            }
        }
        public void NOT(params string[] expressions)
        {
            for (int i = 0; i < expressions.Length; i++)
            {
                if (expressions[i].Trim() != "")
                {
                    if (totalExpression != "")
                    {
                        totalExpression += "\r\nAND NOT ";
                    }
                    else
                    {
                        totalExpression += "\r\nNOT ";
                    }
                    totalExpression += "(" + expressions[i] + ")";
                }
            }
        }
        public void ANDNot(params string[] expressions)
        {
            for (int i = 0; i < expressions.Length; i++)
            {
                if (expressions[i].Trim() != "")
                {
                    if (totalExpression != "")
                    {
                        totalExpression += "\r\nAND NOT ";
                    }
                    totalExpression += "(" + expressions[i] + ")";
                }
            }
        }
        public void ORNot(params string[] expressions)
        {
            for (int i = 0; i < expressions.Length; i++)
            {
                if (expressions[i].Trim() != "")
                {
                    if (totalExpression != "")
                    {
                        totalExpression += "\r\nOR NOT ";
                    }
                    totalExpression += "(" + expressions[i] + ")";
                }
            }
        }
        public void Clear()
        {
            this.totalExpression = "";
        }
        public override string ToString()
        {
            return totalExpression;
        }
    }
    [Serializable]
    public class OrderByClause
    {
        private string totalExpression = "";
        private int count = 0;
        private int newlineLimit = 5;
        public int NewlineLimit
        {
            get { return newlineLimit; }
            set { newlineLimit = value; }
        }
        public void Add(params string[] expressions)
        {
            for (int i = 0; i < expressions.Length; i++)
            {
                if (expressions[i].Trim() != "")
                {
                    this.count++;
                    if (totalExpression != "")
                    {
                        totalExpression += ", ";
                    }
                    if (this.count == this.newlineLimit)
                    {
                        this.count = 0;
                        totalExpression += "\r\n";
                    }
                    totalExpression += expressions[i];
                }
            }
        }
        public void Clear()
        {
            this.count = 0;
            this.totalExpression = "";
        }
        public override string ToString()
        {
            return totalExpression;
        }
    }

}
